package com.zjzywl.smoke.repository.impl;

import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import org.springframework.stereotype.Repository;

import com.zjzywl.smoke.entity.Device;
import com.zjzywl.smoke.entity.DeviceStatus;

@Repository
public class DeviceStatusRepositoryImpl {
	@PersistenceContext
	private EntityManager em;

	public List getTroubleDevice(String installDate, String returnTime) {
		String sql = "select ds.returnTime,device from DeviceStatus ds right join ds.device device With ds.returnTime >='"
				+ returnTime + "' " + " where device.installationDate<'" + installDate + " and ds.returnTime is null";
		Query query = em.createQuery(sql);
		return query.getResultList();
	}

	public List getDSByUser(String userId) {
		String sql = " from DeviceStatus a where a.device in ("
				+ " SELECT b.device FROM OrgDev b , SysUser c where b.organization.id=c.org.id " + " and c.userId = "
				+ userId + ")";

		Query query = em.createQuery(sql);
		return query.getResultList();
	}

	public List getDSByUserAndDeviceType(String userId, String deviceTypeId, String status, String devCode) {
		String sql = "from DeviceStatus a where a.device in ("
				+ " SELECT b.device FROM OrgDev b , SysUser c where b.organization.id=c.org.id " + " and c.userId = "
				+ userId + " and b.device.deviceType.deviceTypeId=" + deviceTypeId + ")";
		if (validStr(status)) {
			sql += " and a.status=" + status;
		}
		if (validStr(devCode)) {
			sql += " and a.devCode='" + devCode+"'";
		}
		sql += " and a.device.Remove=null  order by a.device.fireCount DESC ";
		
		System.out.println(sql);
		Query query = em.createQuery(sql);
		return query.getResultList();
	}

	public List getTHByUserAndDeviceType(String b, String e, String userId, String deviceTypeId) {
		String sql = " select a.device.longitude,a.device.latitude,a.device.fireCount from DeviceStatus a where a.device in ("
				+ " SELECT b.device FROM OrgDev b , SysUser c where b.organization.id=c.org.id " + " and c.userId = "
				+ userId + " and b.device.deviceType.deviceTypeId=" + deviceTypeId + ")";
		sql += "  and a.returnTime >= '" + b + "' and a.returnTime <='" + e + "' ";
		Query query = em.createQuery(sql);
		return query.getResultList();
	}

	public List getDSByUserAndDeviceTypeAndStatus(String userId, String status, String deviceTypeId) {
		String sql = " from DeviceStatus a where a.status =" + status + " and a.device in ("
				+ " SELECT b.device FROM OrgDev b , SysUser c where b.organization.id=c.org.id " + " and c.userId = "
				+ userId + " and b.device.deviceType.deviceTypeId=" + deviceTypeId + ")";

		Query query = em.createQuery(sql);
		return query.getResultList();
	}

	public List getDSCountByUserAndDeviceType(String userId, String deviceTypeId) {
		String sql = "select count(a) from DeviceStatus a where a.device in ("
				+ " SELECT b.device FROM OrgDev b , SysUser c where b.organization.id=c.org.id " + " and c.userId = "
				+ userId + " and b.device.deviceType.deviceTypeId=" + deviceTypeId + ")";

		Query query = em.createQuery(sql);
		return query.getResultList();
	}

	public List getDSCountByUserAndDeviceTypeAndStatus(String userId, String status, String deviceTypeId,
			String startTime, String endTime) {
		String sql = "select a.device.deviceName , a.device.address ,a.status,a.beginTime ,a.disposePeople ,a.remark ,a.disposeTime ,a.disposeContent from Alarm a where  ";

		if (status.equals("all")) {
			sql += "a.alarmType.alarmTypeId in(2,3)";
		} else {
			sql += "a.alarmType.alarmTypeId=" + status;
		}

		sql += " and a.device in (" + " SELECT b.device FROM OrgDev b , SysUser c where b.organization.id=c.org.id "
				+ " and c.userId = " + userId + " and b.device.deviceType.deviceTypeId=" + deviceTypeId + ")"
				+ " and a.beginTime >= '" + startTime + "' and a.beginTime<='" + endTime + "'";
		Query query = em.createQuery(sql);
		System.out.println(sql);
		return query.getResultList();
	}

	public List getDSCountByUserAndDeviceCode(String userId, String deviceCode) {
		String sql = "select count(a) from DeviceStatus a where a.device.devCode=" + deviceCode + " and a.device in ("
				+ " SELECT b.device FROM OrgDev b , SysUser c where b.organization.id=c.org.id " + " and c.userId = "
				+ userId + " and b.device.devCode=" + deviceCode + ")";

		Query query = em.createQuery(sql);
		return query.getResultList();
	}

	
	public List<DeviceStatus> getDeviceByPosition(double minlat, double maxlat, double minlng, double maxlng) {
		String sql = "select a from DeviceStatus a where a.device.latitude >='"+minlat+"' and a.device.latitude <='"+maxlat+"' and a.device.longitude>='"+minlng+"' and a.device.longitude<='"+maxlng
				+"' and a.device.Remove=null and a.device.deviceType.deviceTypeId in ('003','009','022')";
		System.out.println(sql);
		Query query = em.createQuery(sql);
		return query.getResultList();
	}

	private Boolean validStr(String value) {
		return (value != null) && (value.length() != 0) && (!value.equals("null")) && (!value.equals("%null%"));
	}
	public int findDeviceCountByUserAndDeviceTypeAndStatus(String userId, String devicetypeid, String status) {
		return 0;
		
	}
}
